﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;

namespace j1
{
    public class SchoolDataAccess
    {
        public static void AddSchool(School school)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "INSERT INTO School (Name) VALUES (@name)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", school.Name);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Added School: {school.Name}");
        }

        public static void DeleteSchool(int schoolId)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "DELETE FROM School WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", schoolId);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Deleted School with Id: {schoolId}");
        }

        public static void UpdateSchool(School school)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "UPDATE School SET Name = @name WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", school.Name);
                    command.Parameters.AddWithValue("@id", school.Id);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Updated School with Id: {school.Id}");
        }

        public static School GetSchool(int schoolId)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "SELECT * FROM School WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", schoolId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new School
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString()
                            };
                        }
                    }
                }
            }
            return null;
        }

        public static List<School> GetAllSchools()
        {
            var schools = new List<School>();
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "SELECT * FROM School";
                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            schools.Add(new School
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString()
                            });
                        }
                    }
                }
            }
            return schools;
        }
    }

}
